package cn.hicard.risk.dao.bd.impl;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import cn.hicard.core.Page;
import cn.hicard.core.PageResult;
import cn.hicard.core.Where;
import cn.hicard.risk.dao.BaseDao;
import cn.hicard.risk.dao.bd.TradefileinfoDao;
import cn.hicard.risk.entity.BdTradefileinfo;

@Repository
public class TradefileinfoDaoImpl extends BaseDao<BdTradefileinfo> implements TradefileinfoDao {
	
	@Override
	public void saveList(List<BdTradefileinfo> list) throws Exception {
		super.save(list);
	}
	
	@Override
	public boolean checkExistSysno(String sysno, String transdate) throws Exception {
		String hql = "SELECT COUNT(sysno) FROM BdTradefileinfo WHERE sysno=? AND transdate=?";
		return super.findCountByHql(hql, sysno, transdate) > 0;
	}
		
	/**
	 * 根据where据表述的条件查询实体集合，HQL与SQL同时支持，当心where中写错字段
	 */
	@Override
	public List<BdTradefileinfo> findByWhere(Where where) throws Exception{
		if(where.isHQL()){
			String hql = "FROM BdTradefileinfo WHERE 1=1 " + where.queryString() + " ORDER BY transdate";
			return super.findByHql(hql, where.getParams());
		}else{
			// 注意：如果数据库字段与实体相同则SELECT 与 FROM 之间可以直接用一个*代替
			String sql = "SELECT "
				+"pk_tradefileinfo {BdTradefileinfo.pkTradefileinfo}"+"importdatetime {BdTradefileinfo.importdatetime}"
				+"transdate {BdTradefileinfo.transdate}"+"termno {BdTradefileinfo.termno}"
				+"merchname {BdTradefileinfo.merchname}"+"transtime {BdTradefileinfo.transtime}"
				+"cardno {BdTradefileinfo.cardno}"+"cardbank {BdTradefileinfo.cardbank}"
				+"transmoney {BdTradefileinfo.transmoney}"+"fee {BdTradefileinfo.fee}"
				+"clearmoney {BdTradefileinfo.clearmoney}"+"sysno {BdTradefileinfo.sysno}"
				+"systraceno {BdTradefileinfo.systraceno}"+"channel {BdTradefileinfo.channel}"
				+"transtypename {BdTradefileinfo.transtypename}"+"transtype {BdTradefileinfo.transtype}"
				+"cardtypename {BdTradefileinfo.cardtypename}"+"cardtype {BdTradefileinfo.cardtype}"
				+"area {BdTradefileinfo.area}"+"wpmerchno {BdTradefileinfo.wpmerchno}"
				+"merchno {BdTradefileinfo.merchno}"+"reserve1 {BdTradefileinfo.reserve1}"
				+"reserve2 {BdTradefileinfo.reserve2}"+"reserve3 {BdTradefileinfo.reserve3}"
				+"callflag {BdTradefileinfo.callflag} "
				+"FROM bd_tradefileinfo WHERE 1=1 " + where.queryString() + " ORDER BY transdate";
			return super.findBySql(sql, where.getParams());
		}
	}
		
	public PageResult<BdTradefileinfo> search(Where where, Page page) throws Exception {
		String hqlData = "FROM BdTradefileinfo WHERE 1=1 ";
		String hqlCount = "SELECT COUNT(*) FROM BdTradefileinfo WHERE 1=1 ";
		hqlData += where.queryString();
		hqlCount += where.queryString();
		PageResult<BdTradefileinfo> result = new PageResult<BdTradefileinfo>();
		result.setResultCount(super.findCountByHql(hqlCount, where.getParams()));
		result.setDataList(super.findByHqlPage(hqlData, page, where.getParams()));
		
		return result;
	}
	

	/**
	 * 在指定日期内，从传统POS交易数据中筛选需要紧急调单的数据
	 * @param date
	 * @return
	 * @throws Exception
	 */	
	public List<BdTradefileinfo> findCallorder(String date)
		throws Exception {
		// 查询的最终结果
		List<BdTradefileinfo> list = new ArrayList<BdTradefileinfo>();		
		// 查询出符合条件的各数据主键集合，去除重复的，用以检测是否已经存在
		Set<Integer> pkSet = new HashSet<Integer>();
		// 统计的[商户部分条件]数据
		List<Object[]> merchCondition = null;
		
		
		// 注2147483647为JAVA中，int型的最大值。//金额值用数字型 比较大小
		String hql = " FROM BdTradefileinfo " 
			 +" WHERE cardtype=1 AND (transmoney+0)>=(?+0) AND transdate=? "
			 +" AND wpmerchno=? AND termno=? AND cardno=? "
			 +" ORDER BY transtime ASC ";
			 // +"LIMIT ?,2147483647";		
		
		Session session = getHibernateTemplate().getSessionFactory().openSession();
				
		try {
			Query query = session.createQuery(hql);
			
			// 1.同一天内，同一商户，同一终端，同一信用卡，单笔>=10，连续>=4，从第4笔开始
			merchCondition = statistics(10, date, 4, 0); // 不需计算累计金额，因此最后一个参数为0，即累计金额大于等于0就行
			// 不需要前3条
			query.setFirstResult(3);
			query.setMaxResults(Integer.MAX_VALUE);	
			for (Object[] arr : merchCondition) {
				// 参数顺序：交易金额,交易日期,商户号,终端号,卡号
				query.setParameter(0, 10);
				query.setParameter(1, date);
				query.setParameter(2, arr[0]);
				query.setParameter(3, arr[1]);
				query.setParameter(4, arr[2]);

				@SuppressWarnings("unchecked")
				List<BdTradefileinfo> tmpList = query.list();
				for (BdTradefileinfo trade : tmpList) {
					if(pkSet.contains(trade.getPkTradefileinfo())){
						continue;
					}else{
						pkSet.add(trade.getPkTradefileinfo());
						list.add(trade);
					}					
				}
			}	
			
			
			//2.同一天内，同一商户，同一终端，同一信用卡，单笔>=5000，连续>=2，从第2笔开始
			merchCondition = statistics(5000, date, 2, 0); // 不需计算累计金额，因此最后一个参数为0，即累计金额大于等于0就行
			// 不需要前1条
			query.setFirstResult(1);
			query.setMaxResults(Integer.MAX_VALUE);	
			for (Object[] arr : merchCondition) {
				// 参数顺序：交易金额,交易日期,商户号,终端号,卡号
				query.setParameter(0, 5000);
				query.setParameter(1, date);
				query.setParameter(2, arr[0]);
				query.setParameter(3, arr[1]);
				query.setParameter(4, arr[2]);

				@SuppressWarnings("unchecked")
				List<BdTradefileinfo> tmpList = query.list();
				for (BdTradefileinfo trade : tmpList) {
					if(pkSet.contains(trade.getPkTradefileinfo())){
						continue;
					}else{
						pkSet.add(trade.getPkTradefileinfo());
						list.add(trade);
					}					
				}
			}
			
			// 3.同一天内，同一商户，同一终端，同一信用卡，累计 超>=20000，从达到20000笔开始的全部调单
			merchCondition = statistics(0, date, 0, 20000); // 不计算单笔金额，也不计算累计笔，只统计累计值			
			hql = "FROM BdTradefileinfo WHERE cardtype=1 AND transdate=?  AND wpmerchno=? AND termno=? AND cardno=? ORDER BY transtime ASC ";
			query = session.createQuery(hql);
			for (Object[] arr : merchCondition) {
				// 参数顺序：交易金额,交易日期,商户号,终端号,卡号
				query.setParameter(0, date); // 同一天
				query.setParameter(1, arr[0]); // 同一商户
				query.setParameter(2, arr[1]); // 同一终端
				query.setParameter(3, arr[2]); // 同一信用卡

				@SuppressWarnings("unchecked")
				List<BdTradefileinfo> tmpList = query.list();
				/* 由于List是序的集合，查询结果也是按交易时间(同一天内的时间)升序排列，
				 * 逐个累加该临时集合中的每一笔交易，记录下刚好达到或超过20000的开始下标，
				 * 因此从这个下标开始就是开始>=20000之后的数据了
				 */
				double totalmoney = 0;				
				for (BdTradefileinfo trade : tmpList) {					
					totalmoney += Double.valueOf(trade.getTransmoney());
					if(totalmoney>=20000.00){
						if(pkSet.contains(trade.getPkTradefileinfo())){
							continue;
						}else{
							pkSet.add(trade.getPkTradefileinfo());
							list.add(trade);
						}
					}				
				}
			}
			
		} finally {
			session.close();
		}
		
		return list;
	}
	
	/**
	 * 统计符合指定条件的[商户号],[终端号],[卡号]数据集合
	 * @param transmoney 单笔交易金额
	 * @param transdate 交易日期
	 * @param transcount 合计笔数
	 * @param totalmoney 合计金额
	 * @return 返回的数组字段依次是: [商户号],[终端号],[卡号],[合计笔数],[合计金额]
	 * @throws Exception
	 */
	private List<Object[]> statistics(int transmoney, String transdate, int transcount, int totalmoney) throws Exception{
		
		String sql = " SELECT wpmerchno,termno,cardno,COUNT(wpmerchno) AS transcount,SUM(transmoney) AS totalmoney " 
						+" FROM bd_tradefileinfo "
						+" WHERE cardtype='1' AND transmoney>=" + transmoney + " AND transdate=" + transdate
						+" AND callflag<>0 "
						+" GROUP BY merchno,termno,cardno "
						+" HAVING transcount>=" + transcount + " AND totalmoney>=" + totalmoney;
		@SuppressWarnings("unchecked")
		List<Object[]> info = super.findFieldsBySql(sql);
		if(info == null) {
			info = new ArrayList<Object[]>();
		}
		return info;
	}
	
  
	
	
			
	/**
	 * 根据 [商户号merchno] 和 [终端号termno] 汇总 [交易数量] 和 [交易额]<BR/>
	 * 返回的Map key：merchno,merchname,transcount,summoney,area, termno 
	 * @param where
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> findCollect(Where where)throws Exception{
		String sql = "SELECT merchno, merchname, COUNT(termno) as transcount, SUM(transmoney) as summoney, area, termno FROM bd_tradefileinfo WHERE 1=1 " + where.queryString() + " GROUP BY merchno,termno ORDER BY merchno,termno";
		return super.findMapBySql(sql, where.getParams());

	}
	
	public List<String> findLastDate() throws Exception{
		String hql = "SELECT MAX(transdate) FROM BdTradefileinfo";
		List<String> findFieldByHql = super.findFieldsByHql(hql);
		return findFieldByHql;
	}

}
